package com.hnkywl.sts.dao.organ;


import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.entity.organ.Qyjg;
import com.hnkywl.sts.entity.organ.Zyf;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-3-22
 * Time: 下午4:31
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class QyjgDao extends SimpleHibernateTemplate<Qyjg, Long> {
    /**
     * 查询企业机构列表
     *
     * @param qc       企业机构全称
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Qyjg> findQyjgList(String qc, String jc, String lxr, int pageNo, int pageSize) {
        String hql = null;
        String lxrHql = "";
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Qyjg> page = new Page<Qyjg>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (qc != null && !qc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" qc like ? ");
            param.add("%" + qc.trim() + "%");
        }
        if (jc != null && !jc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append("( jc like ? or qc like ?)");
            param.add("%" + jc.trim() + "%");
            param.add("%" + jc.trim() + "%");
        }
        if (lxr != null && !lxr.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" lxr like ? ");
            param.add("%" + lxr.trim() + "%");
        }
//        if (lxrId != null) {
//            if (condition.length() != 0)
//                condition.append(" and ");
//            lxrHql = ", Lxr lxr ";
//            condition.append(" qyjg in elements(lxr.qyjgList) and lxr.id = ? ");
//            param.add(lxrId);
//        }
//        if (lxrXm != null&&!lxrXm.isEmpty()) {
//            if (condition.length() != 0)
//                condition.append(" and ");
//            lxrHql = ", Lxr lxr ";
//            condition.append(" qyjg in elements(lxr.qyjgList) and lxr.xm like ? ");
//            param.add("%" + lxrXm + "%");
//        }

        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" (lskh = false or lskh is null) ");
        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" type = ? ");
        param.add("qyjg");
        //==============记录数量===============
        hql = "select count(*) from Qyjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Qyjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition + "order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public Page<Qyjg> findLskhList(String qc, String jc, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Qyjg> page = new Page<Qyjg>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (qc != null && !qc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" qc like ? ");
            param.add("%" + qc.trim() + "%");
        }
        if (jc != null && !jc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" jc like ? ");
            param.add("%" + jc.trim() + "%");
        }

        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" lskh = ? ");
        param.add(true);
        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" type = ? ");
        param.add("qyjg");
        //==============记录数量===============
        hql = "select count(*) from Qyjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Qyjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition + "order by lastUpdated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public List<Qyjg> findAallQyjg() {
        String hql = "from Qyjg where deleted = 0";
        return find(hql);
    }

    public List<Map> findAllGysByNbgsId(Long nbgsId) {
        String sql = "    select distinct qyjg.id as \"qyjg.id\",qyjg.jc as \"qyjg.jc\" " +
                "        from t_lj_sy sy  " +
                "          left join t_sys_lj lj on (sy.lj_id = lj.id) " +
                "          left join t_sys_gsjg gsjg on (lj.jyjg_id = gsjg.id) " +
                "          left join t_sys_gsjg qyjg on (sy.sy_id = qyjg.id and qyjg.type = 'qyjg') " +
                "          where lj.deleted = 0 and gsjg.id = ? ";
        return (List<Map>) this.createSQLQuery(sql, nbgsId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public List<Map> findAllGys() {
        String sql = "    select distinct qyjg.id as \"qyjg.id\",qyjg.jc as \"qyjg.jc\" " +
                "        from t_lj_sy sy  " +
                "          left join t_sys_lj lj on (sy.lj_id = lj.id) " +
                "          left join t_sys_gsjg gsjg on (lj.jyjg_id = gsjg.id) " +
                "          left join t_sys_gsjg qyjg on (sy.sy_id = qyjg.id and qyjg.type = 'qyjg') " +
                "          where lj.deleted = 0  ";
        return (List<Map>) this.createSQLQuery(sql).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public List<Map> findAllKhByNbgsId(Long nbgsId) {
        String sql = "    select distinct qyjg.id as \"qyjg.id\",qyjg.jc as \"qyjg.jc\" " +
                "        from t_lj_xy xy  " +
                "          left join t_sys_lj lj on (xy.lj_id = lj.id) " +
                "          left join t_sys_gsjg gsjg on (lj.jyjg_id = gsjg.id) " +
                "          left join t_sys_gsjg qyjg on (xy.xy_id = qyjg.id and qyjg.type = 'qyjg') " +
                "          where lj.deleted = 0 and gsjg.id = ? ";
        return (List<Map>) this.createSQLQuery(sql, nbgsId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
                .list();
    }

    public List<Map> findAllKsyJkdByNbgsId(Long nbgsId) {
        String sql = "   select qyjg.id as \"qyjg.id\",qyjg.jc as \"qyjg.jc\"  " +
                " from t_sys_gsjg qyjg where id in  " +
                "  (select gys_id from t_biz_jkd where zt = 'ysp' and yfje < zfje  and jkgs_id = ? ) ";
        return (List<Map>) this.createSQLQuery(sql, nbgsId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
                .list();
    }


    /**
     * 对企业机构名称进行校验
     *
     * @param
     * @param id 企业机构ID
     * @return
     */
    public Boolean checkUnique(Long id, Object propertyValue, String propertyName) {
        String hql;
        if (propertyValue == null)
            return false;
        if (id == null) {
            //修改检验
            hql = "from Qyjg where " + propertyName + " = ? and type = 'qyjg'";
            return this.existsQuery(hql, propertyValue);
        } else {
            //新增检验
            hql = "from Qyjg where " + propertyName + " = ? and id <> ? and type = 'qyjg'";
            return this.existsQuery(hql, propertyValue, id);
        }
    }

    /**
     * 查询所有路径下的企业机构
     *
     * @return
     */
    public Set<Qyjg> findAllRootQyjg() {
        Set<Qyjg> qyjgList = new HashSet<Qyjg>();
        String hql = "select distinct sy from Lj lj";
        hql += RegionDao.createDataRegion("where ", "lj");
        Query query = this.createQuery(hql);
        qyjgList.addAll(query.list());
        hql = "select distinct xy from Lj lj";
        hql += RegionDao.createDataRegion("where ", "lj");
        query = this.createQuery(hql);
        qyjgList.addAll(query.list());
        return qyjgList;
    }

    /**
     * 查询所有的企业机构的jc
     *
     * @param jc
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Qyjg> findQyjgJcList(String jc, int pageNo, int pageSize) {
        Page<Qyjg> page = new Page<Qyjg>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("lastUpdated");
        if (StringUtils.isNotEmpty(jc)) {
            return this.findByCriteria(page, new Criterion[]{Restrictions.like("jc", jc, MatchMode.ANYWHERE), Restrictions.eq("zt", "ysp"), Restrictions.eq("type", "qyjg")});
        } else
            return this.findByCriteria(page, new Criterion[]{Restrictions.eq("zt", "ysp")});
    }


    /**
     * 查詢企業機構不含物流商
     *
     * @param jc
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List<Map> findQyjgJcListNotWls(String jc, int pageNo, int pageSize) {
        String select = "select  g.id  as \"id\",  g.jc  as  \"jc\" ";
        String from = " from t_sys_gsjg g where g.type in ('qyjg') and g.deleted = 0";
        StringBuilder where = new StringBuilder();
        if (jc != null) {
            where.append(" and ");
            where.append("g.jc like :jc");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where);
        if (jc != null) {
            query.setString("jc", "%" + jc.trim() + "%");
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }

    public List<Map> findQyjgQcListNotWls(String jc, int pageNo, int pageSize) {
        String select = "select  g.id  as \"id\",  g.qc  as  \"qc\" ";
        String from = " from t_sys_gsjg g where g.type in ('qyjg') and g.deleted = 0";
        StringBuilder where = new StringBuilder();
        if (jc != null) {
            where.append(" and ");
            where.append("g.qc like :qc");
        }
        SQLQuery query = getSession().createSQLQuery(select + from + where);
        if (jc != null) {
            query.setString("qc", "%" + jc.trim() + "%");
        }
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return query.list();
    }


    /**
     * 查找所有非零售客户
     *
     * @return
     */
    public List<Qyjg> findAllLjQyjg() {
        return find("from Qyjg where sjjg = null and  lskh = false");
    }

    /**
     * 查询零售类型企业机构
     *
     * @return
     */
    public List<Qyjg> findAllLxQyjg() {
        return find("from Qyjg where  type = 'qyjg' and sjjg = null and  sfls = true or lskh = true");
    }

    /**
     * 根据属性查找零售客户
     *
     * @param propertyName
     * @return
     */
    public List<Qyjg> findLskhByPropertyName(String propertyName, String propertyValue) {
        String hql = "from Qyjg where type = 'qyjg' and sjjg = null and lskh = true and " + propertyName + " = ?";
        return createQuery(hql, propertyValue).list();
    }

    /**
     * 获取所有的联系人
     *
     * @return
     */
    public List<Qyjg> findAllRootLxr() {
        return find("from Qyjg where  type = 'qyjg' and sjjg = null and lskh = false");
    }

    public List<Qyjg> findAllRootLskh() {
        return find("from Qyjg where  type = 'qyjg' and sjjg = null and lskh = true");
    }

    /**
     * @param key
     * @param size
     * @return
     */
    public List<Qyjg> findAllSy(String key, Integer size) {
        String hql = "select distinct b from Lj a left join a.sy b ";
        ArrayList param = new ArrayList();
        if (StringUtils.isNotEmpty(key)) {
            hql += " where concat(b.qc,b.zjm) like ?";
            param.add("%" + key + "%");
        }
        return createQuery(hql, param.toArray()).setMaxResults((size != null ? size : 20)).setFirstResult(0).list();
    }


    /**
     * @param key
     * @param size
     * @return
     */
    public List<Qyjg> findAllXy(String key, Integer size) {
        String hql = "select distinct b from Lj a left join a.xy b ";
        ArrayList param = new ArrayList();
        if (StringUtils.isNotEmpty(key)) {
            hql += " where concat(b.qc,b.zjm) like ?";
            param.add("%" + key + "%");
        }
        return createQuery(hql, param.toArray()).setMaxResults((size != null ? size : 20)).setFirstResult(0).list();
    }

    public List<Map> findByZjmOrJcOrQc(String zjm, String jc, String qc, Long id, Integer maxRows) {
        String hql = "select new map(id as id,qc as qc,jc as jc) from Qyjg";
        ArrayList param = new ArrayList();
        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotEmpty(zjm)) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("zjm like ?");
            param.add("%" + zjm + "%");
        }
        if (StringUtils.isNotEmpty(qc)) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("qc like ?");
            param.add("%" + qc + "%");
        }
        if (StringUtils.isNotEmpty(jc)) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("jc like ?");
            param.add("%" + jc + "%");
        }
        if (id != null) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("id=?");
            param.add(id);
        }
        hql += (where.length() != 0 ? " where " : "") + where;
        return createQuery(hql, param.toArray()).setMaxResults((maxRows != null ? maxRows : 20)).setFirstResult(0).list();
    }


    public List<Map> findLskhByZjmOrJcOrQc(String zjm, String jc, String qc, Long id, Integer maxRows) {
        String hql = "select new map(id as id,qc as qc,jc as jc) from Qyjg ";
        ArrayList param = new ArrayList();
        StringBuilder where = new StringBuilder();
        if (StringUtils.isNotEmpty(zjm)) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("zjm like ?");
            param.add("%" + zjm + "%");
        }
        if (StringUtils.isNotEmpty(qc)) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("qc like ?");
            param.add("%" + qc + "%");
        }
        if (StringUtils.isNotEmpty(jc)) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("jc like ?");
            param.add("%" + jc + "%");
        }
        if (id != null) {
            if (where.length() != 0)
                where.append(" or ");
            where.append("id=?");
            param.add(id);
        }
        hql += (where.length() != 0 ? " where ( " + where + " ) " : "") + "  and  sjjg = null and ( lskh = true or sfls = true ) ";
        return createQuery(hql, param.toArray()).setMaxResults((maxRows != null ? maxRows : 20)).setFirstResult(0).list();
    }

    public Qyjg findQyjgByQc(String qcName) {
        List<Qyjg> qyjgList = find("from Qyjg where qc = ? ", qcName);
        return CollectionUtils.isEmpty(qyjgList) ? null : qyjgList.get(0);
    }


    public Zyf findZyfByQc(String qcName) {
        List<Zyf> qyjgList = find("from Zyf where qc = ? ", qcName);
        return CollectionUtils.isEmpty(qyjgList) ? null : qyjgList.get(0);
    }


    public List<Map> findQyjgList(Long qyjgId) {
        String sql = " select  qy.id as id,\n" +
                "           qy.qc as qc,\n" +
                "           qy.jc as jc\n" +
                "    from t_sys_gsjg qy  \n" +
                "     left join t_sys_khh khh on khh.qyjg_id=qy.id\n" +
                "     left join t_sys_ejzh  ej on ej.khh_id=khh.id\n" +
                "     where  qy.id=?";
        return (List<Map>) this.createSQLQuery(sql, qyjgId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }


    public Page<Qyjg> findQyjgList1(String qc, String jc, String lxr, int pageNo, int pageSize) {
        String hql = null;
        String lxrHql = "";
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Qyjg> page = new Page<Qyjg>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (qc != null && !qc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" qc like ? ");
            param.add("%" + qc.trim() + "%");
        }
        if (jc != null && !jc.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append("( jc like ? or qc like ?)");
            param.add("%" + jc.trim() + "%");
            param.add("%" + jc.trim() + "%");
        }
        if (lxr != null && !lxr.isEmpty()) {
            if (condition.length() != 0)
                condition.append(" and ");
            condition.append(" lxr like ? ");
            param.add("%" + lxr.trim() + "%");
        }


        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" (lskh = false or lskh is null) ");
        if (condition.length() != 0)
            condition.append(" and ");
        condition.append(" type = ? ");
        param.add("qyjg");
        //==============记录数量===============
        hql = "select count(*) from Qyjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition + " and zt='ysp'";
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Qyjg ";
        if (condition.length() != 0)
            hql = hql + " where " + condition + " and zt='ysp' order by dateCreated desc" + RegionDao.createDataRegion("and");
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

/*    hql = "from Sfk where sfklx in (3,4) " +

            " and sfhz = 0 and (createUser.id is not null ) "

            + RegionDao.createDataRegion("and");


    hql = "from Sfk where sfklx in (3,4) " +

            " and sfhz = 0 and (createUser.id is not null ) "

            + RegionDao.createDataRegion("and");
    if (condition.length() != 0)
    hql = hql + " and " + condition;
    hql = hql + " order by sfkrq desc";*/
}
